#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:robot
@file: tap.py 
@version:
@time: 2022/03/17 
"""
# !usr/bin/env python
# -*- coding:utf-8 _*-
from importlib import import_module
from pprint import pprint

from TapUtil.logfunc.logger import logger

""" 
@author:robot
@file: connector_demo.py 
@version:
@time: 2022/03/17 
"""
import inspect
import os
import sys

sys.path.append(os.getcwd())
import fire

from TapClientConnector import *


def get_classes(arg):
    classes = {}
    cls_members = inspect.getmembers(arg, inspect.isclass)
    for name, _ in cls_members:
        classes.update({name: _})
    return classes


class TapConnectorPythonClient:

    def __init__(self,
                 db_info_file='db_info',
                 source_type='',
                 name='',
                 sql='',
                 table='',
                 type_list='',
                 ip='',
                 port='', ):
        if db_info_file == 'db_info':
            import db_info as _
            self.db_info_file = _
        elif db_info_file == 'db_info_cloud':
            import db_info_cloud as _
            self.db_info_file = _
        elif db_info_file:
            _ = import_module('TapDataSourceInfo', db_info_file)
        else:
            print('not found this file')
        if not type_list:
            type_list = [
                'Mysql',
                'MongoDB',
                'Oracle',
                'Postgres',
                'Sqlserver'
            ]
        self.sql = sql
        self.table = table
        self.source_type = source_type
        self.name = name
        self.type_list = type_list
        self.ip = ip
        self.port = port

    def show_db_type(self):
        pprint(get_classes(self.db_info_file))

    def show_member(self):
        class_info = dir(get_classes(self.db_info_file).get(self.source_type))
        tmp = []
        for i in class_info:
            if '__' not in i:
                tmp.append(i)
        pprint(tmp)

    def show_attr(self):
        pprint(getattr(get_classes(self.db_info_file).get(self.source_type), self.name))

    def connect_test(self):
        ip = self.ip
        port = self.port
        for one_type in self.type_list:
            type_list = dir(get_classes(self.db_info_file).get(one_type))

            _type_list = [i for i in type_list if '__' not in i]
            _type_list = [i for i in _type_list if 'all_list' not in i]
            _type_list = [i for i in _type_list if 'ALL' not in i]
            _type_list = [i for i in _type_list if 'database_type' not in i]
            _type_list = [i for i in _type_list if 'others' not in i]

            for one_name in _type_list:
                tmp = {}
                db_info = getattr(get_classes(self.db_info_file).get(one_type), one_name)
                tmp = db_info
                if not tmp:
                    pass
                else:
                    if ip and not port:
                        if ip in db_info.get('database_host'):
                            tmp = db_info
                        else:
                            tmp = {}
                    elif port and not ip:
                        if str(port) == db_info.get('database_port'):
                            tmp = db_info
                        else:
                            tmp = {}
                    elif ip and port:
                        if ip in db_info.get('database_host') and str(port) == db_info.get('database_port'):
                            tmp = db_info
                        else:
                            tmp = {}
                    if tmp:
                        print()
                        print()
                        logger.warning(f'连接名: {tmp.get("name")}')
                        if ip:
                            logger.info(f"database_host: {db_info.get('database_host')}")
                        if port:
                            logger.info(f"database_port: {db_info.get('database_port')}")
                        print(f'{tmp}')
                        try:
                            connector = TapClientConnector(tmp)
                            connector.tapclient_connector.close()
                        except:
                            pass

    def query(self):
        """
        查询语句结果获取
        :return:
        """
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        if not self.sql:
            raise 'sql not set'
        return connector.tapclient_connector.query(self.sql)

    def common_query(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        if not self.table:
            raise 'table Not set'
        select = connector.tapclient_connector.generate_select(table=self.table)
        return connector.tapclient_connector.query(select)

    def common_count(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        count = connector.tapclient_connector.generate_select(column='count(*)', table=self.table)
        return connector.tapclient_connector.query(count)

    def common_drop(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        drop = connector.tapclient_connector.generate_drop(table=self.table)
        return connector.tapclient_connector.exec(drop)

    def drop_all_table(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        import asyncio
        async_run = asyncio.get_event_loop().run_until_complete
        async_run(self._drop_all_table_(connector, table_list))

    @staticmethod
    async def _drop_all_table_(connector, table_list):
        for table in table_list:
            drop_operate = connector.tapclient_connector.generate_drop(table)
            connector.tapclient_connector.exec(drop_operate)

    def drop_table_by_key(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        import asyncio
        async_run = asyncio.get_event_loop().run_until_complete
        async_run(self._drop_table_by_key(connector, table_list, self.sql))

    @staticmethod
    async def _drop_table_by_key(connector, table_list, key):
        for table in table_list:
            if key in table:
                drop_operate = connector.tapclient_connector.generate_drop(table)
                connector.tapclient_connector.exec(drop_operate)

    def show_dbs(self):
        """
        查询数据库包含库
        :return:
        """
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.show_dbs()

    def show_tables(self):
        """
        查询数据库表包含表
        :return:
        """
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.show_tables()

    def show_tables_count(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        result = connector.tapclient_connector.show_tables()
        return len(result)

    def execute(self):
        """
        执行语句
        :return:
        """
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.exec(self.sql)

    def pg_create(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        old = connector.tapclient_connector.connector.isolation_level
        connector.tapclient_connector.connector.set_isolation_level(0)
        connector.tapclient_connector.exec(self.sql)
        connector.tapclient_connector.connector.set_isolation_level(old)

    def ms_db_cdc(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.special_execute("""EXEC sys.sp_cdc_enable_db""")

    def ms_table_cdc(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        sql = connector.tapclient_connector.generate_table_cdc(self.table)
        return connector.tapclient_connector.special_execute(sql)

    def ms_table_all_cdc(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        for i in table_list:
            try:
                sql = connector.tapclient_connector.generate_table_cdc(i)
                connector.tapclient_connector.special_execute(sql)
            except:
                pass

    def ms_special_execute(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        return connector.tapclient_connector.special_execute(self.sql)

    def ms_query_db_cdc(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        sql = connector.tapclient_connector.generate_db_cdc_query()
        return connector.tapclient_connector.query(sql)

    def ms_query_all_table_cdc(self):
        db_info = getattr(get_classes(self.db_info_file).get(self.source_type), self.name)
        connector = TapClientConnector(db_info)
        table_list = connector.tapclient_connector.show_tables()
        tmp = []
        for i in table_list:
            sql = f"SELECT [name],is_tracked_by_cdc FROM sys.tables WHERE [name] = N'{i}'"
            result = connector.tapclient_connector.query(sql)
            if not result[1]:
                tmp.append(result)
        print('no cdc table')
        for j in tmp:
            print(j)

    @staticmethod
    def help():
        print("""
            # python connector 命令行查询工具
    
            # 已支持的数据源
            
            - mysql √
            
            - oracle √
            
            - mongodb √
            
            - db2 √
            
            - postgres √
            
            - sqlserver √
            
            - gbase-8s √
            
            - sybase ase ×
            
            - gaussdb200 √
            
            - elasticsearch √
            
            - redis √
            
            - kafka √
            
            - mariadb √
            
            - mysql pxc √
            
            - dameng √,执行机环境未搭建
            
            - hive √
            
            - tcp_udp ×
            
            - mq ×
            
            - hbase ×
            
            - kudu ×
            
            - greenplum √
            
            - tidb √
            
            - hana √
            
            - clickhouse √
            
            - kundb √
            
            - adb_postgres √
            
            - adb_mysql √
            
            - vika ×
            
            - hazelcast_cloud_cluster ×
    
            # 需求
            
            需要一个能够操作数据库的命令行工具
            
            - 创建数据库
            - 查询数据库信息
            - 执行一些配置文件
            - 通过一个配置文件配置数据信息，然后通过命令行去执行DDL，DML等语句
    
            # 方案 fire
            
                pip install fire
            
                https://github.com/google/python-fire/blob/master/docs/guide.md#argument-parsing
            
                集成现有的python connector
            
            # 其他
            
            - 考虑到其他人员使用时，搭建环境麻烦，使用一个flask服务来调度服务器执行机，但是就会有配置数据库信息的问题
            - 或者在配置好环境的183上执行,命令行脚本变为tap.py
            
            ```shell
            ssh root@192.168.1.183 
            Gotapd8!!
            
            cd /home/robot/tap-client-v3
            
            python376 tap.py --source_type=Mysql --name=auto_mysql --sql="create table fire(column int)" execute|query
            ```
    
            希望执行的命令形式
            
            ```cmd
            python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="create table fire(column int)" execute|query
            
            ```
    
    
            ## 脚本位置
            
                TapClientConnector/tap.py
    
            ### 先决条件
            
            - 安装相关的pip库
            - oracle需要依赖client
            - 数据库配置
    
            ```
            需要引入一个数据库配置模块,我默认是这样引入的
            
            import db_info as _
            
            你可以去替换db_info模块来配置自己的数据库配置
            
            db_info模块的应类似这样
    
            class Mysql:
                database_type = "mysql"
            
                mysql_source_test = {
                    "name": "mysql_source_test",
                    "database_type": database_type,
                    "database_host": "47.115.163.10",
                    "database_port": "3306",
                    "database_name": "source",
                    "database_username": "root",
                    "plain_password": "zsj@520wm",
                    "connection_type": source_and_target
                }
    
            引入的模块有许多的class，对应输入参数的source_type
            class的属性，对应输入参数的name
            
            通过--source_type=className --name=attrName 来获取数据源
            
            --sql指定需要执行的语句，execute为执行，query为查询，更多请查看下面的示例
            ```
            
            ## 功能：
            
            支持query、count、show_tables、show_dbs命令
            
            ### 示例
            
            - 查询: 会返回结果，注意不要查询数据量大的表，会卡死
            
            ```cmd
            python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="select * from my2" query
            
            python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="select count(*) from my2" query
            
            python 命令行脚本 --source_type=Mysql --name=auto_mysql show_dbs
            
            python 命令行脚本 --source_type=Mysql --name=auto_mysql show_tables
            ```    
    
            - 执行：不返回结果,会打印执行信息,使用query也一样，只是返回结果为空
    
            ```cmd
            python 命令行脚本 --source_type=Mysql --name=auto_mysql --sql="create table fire(column int)" execute|query
            
            python 命令行脚本--source_type=Mysql --name=auto_mysql --sql="drop table testClient"  execute|query
            ```
    
            ## special
            
            1.sqlserver、postgres、oracle注意小写需要用""引住
            
            2.mongodb的sql是这样定义的
            
            - query
    
            ```cmd
            ("find", (collection_name:str, filter:dict, show:dict))
            ("count", (collection_name:str, filter))
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py 
            --source_type=MongoDB --name=auto_mongo_source --sql="("find", ('mg2',{}))" query
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
            --name=auto_mongo_source --sql="("count", ('mg2',{}))" query
            
            ```
    
            - execute
            
            ```cmd
            ("insert_one", (collection_name:str, document:dict))
            ("insert_many", (collection_name:str, document:list))
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py 
            --source_type=MongoDB --name=auto_mongo_source --sql="("insert_one", ('fire',{'test':'test'}))" execute
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py 
            --source_type=MongoDB --name=auto_mongo_source --sql="("insert_many", ('fire',[{'test':'test'}]))" execute
            
            
            ("update_one", (collection_name:str, filter:dict, update:ditc))
            ("update_many", (collection_name:str, filter:dict, update:dict))
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
            --name=auto_mongo_source --sql="("update_one", ('fire',{'test':'test'}, {'test':'update'}))" execute
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
            --name=auto_mongo_source --sql="("update_many", ('fire',{'test':'test'}, {'test':'update'}))" execute
            
            ("delete_one", (collection_name:str, filter:dict))
            ("delete_many", (collection_name:str, filter:dict))
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
            --name=auto_mongo_source --sql="("delete_one", ('fire',{'test':'test'}))" execute
            
            python TapClientConnector/tapconnector_python_client/connector_demo.py --source_type=MongoDB 
            --name=auto_mongo_source --sql="("delete_many", ('fire',{'test':'test'}))" execute
            
            
            ("drop_db", (collection_name:str,))
            ("drop_table", (collection_name:str,))
            ```
    
            - 更多数据源执行sql说明待完善
                - es的使用方式:支持查询索引、已存在的索引、删除索引
                ```shell
                python376 tap.py --source_type=ES --name=ES_189_target  --sql="c_i_mg2es" query
                python376 tap.py --source_type=ES --name=ES_189_target  --sql="c_i_mg2es" show_tables
                python376 tap.py --source_type=ES --name=ES_189_target  --sql="{'operate': 'drop', 'index': 'index_name'}" execute
                ```
                - redis的使用方式:支持查键值对，已存在的键值对，删除键值对|只支持通过tapdata传输的数据结构
            
            ```shell
              python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="" show_tables
              python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="['1']" query
              python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="['14title1', '1']" query
              python376 tap.py --source_type=Redis --name=auto_redis_target_00 --sql="{'operate': 'drop': 'table': ['14title1', '1']}" execute
            
            
            ```
    
            - kafka的使用方式:只topic的查询，已存在的topic查询，删除topic
            
            ```shell
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="" show_tables
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="CLAIM" query
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'drop': 'topic': ['14title1', '1']}" execute
              新增插入删除
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'insert', 
                                                                                      'topic': 'test', 
                                                                                       topic_data:{'test':'test'}" execute
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'update', 
                                                                                      'topic': 'test', 
                                                                                       topic_data:{'test':'test'}" execute
              python376 tap.py --source_type=Kafka --name=kafka_183_all_topic --sql="{'operate': 'delete', 
                                                                                      'topic': 'test', 
                                                                                       topic_data:{'test':'test'}" execute                                                                                                                                                      
            ```""")


if __name__ == '__main__':
    print("数据源连接测试 python tap.py --type_list=['Mysql',...] --ip='' --port='' connect_test")
    print("指定db_info 配置文件：python tap.py --db_info_file=\"db_info_cloud\" 默认为db_info(db_info_cloud, db_info_v124")
    print("查询数据库种类：python tap.py show_db_type")
    print("查询某种数据库下的成员: python tap.py --source_type=Mysql show_member")
    print("查询查询某个数据库的连接信息 python tap.py --source_type=Mysql --name=auto_mysql show_attr")
    print("命令格式：python tap.py --source_type=--name= --sql=\"\" query|execute|show_dbs|show_tables|show_tables_count")
    print("快速查询：python tap.py --source_type=--name= --table=\"test\" common_query| common_count| common_drop")
    print("sqlserver 更多执行：python tap.py --source_type=--name= --sql="" ms_special_execute")
    print("sqlserver 开启库cdc：python tap.py --source_type=--name=  ms_db_cdc")
    print("sqlserver 查询当前库的cdc：python tap.py --source_type=--name=  ms_query_db_cdc")
    print("sqlserver 开启表cdc：python tap.py --source_type=--name= --table=\"test\" ms_table_cdc")
    print("sqlserver 开启所有表的cdc：python tap.py --source_type=--name=  ms_table_all_cdc")
    print("sqlserver 查询所有表的cdc：python tap.py --source_type=--name=  ms_query_all_table_cdc")
    print("postgres  建库：python tap.py --source_type=--name=  pg_create")
    print("删除源所有表：python tap.py --source_type=--name=  drop_all_table")
    print('删除源所有表：python tap.py --source_type=--name=  --sql="关键字" drop_table_by_key')
    print('python376 tap.py help: 查看更多帮助信息')
    fire.Fire(TapConnectorPythonClient)
